#!/use/bin/python
# coding=utf-8
# 主动服务 同步经验值，从ck到mysql
from queue import Queue
from datetime import date, timedelta
import pymysql
from dbutils.pooled_db import PooledDB
import sys
import time, datetime
import pandas as pd
import requests
import json
from tabulate import tabulate
from datetime import datetime

now = datetime.now()
curTimeYmdHms = now.strftime("%Y-%m-%d %H:%M:%S")
yesterdayYmd = (date.today() + timedelta(days=-1)).strftime("%Y-%m-%d")
yesterdayYYMMdd = (date.today() + timedelta(days=-1)).strftime("%Y%m%d")
yesterdayYYMMdd = yesterdayYYMMdd[2:]
todayYmd = date.today().strftime("%Y-%m-%d")
todayYYMMdd = date.today().strftime("%Y%m%d")
todayYYMMdd = todayYYMMdd[2:]

env = "每日开机步骤 -累计订阅设备"
execRules = (

    {
        "sql": f"""
           select t1.*,t2.* from (
select count(1) '订阅用户',sum(case when (t.switches_state1 & (1 << (18 - 1)))<>0 then 1  else 0 end) 'AI开启',sum(case when (t.switches_state1 & (1 << (18 - 1)))=0 then 1  else 0 end) 'AI未开启' from life_assistant_devices t where (t.switches_state1 & (1 << (1 - 1)))<>0 and 	(t.switches_state1 & (1 << (2 - 1)))<>0)t1 join (
select sum(case when t.wakeup_time is null then 1  else 0 end) '起床时间为空',
sum(case when t.prediction_wakeup_time is null then 1  else 0 end) '预测起床时间为空' 
from proactive_service_devices t where t.active_id in (select tt.active_id from life_assistant_devices tt where (tt.switches_state1 & (1 << (1 - 1)))<>0 and 	(tt.switches_state1 & (1 << (2 - 1)))<>0)) t2 on 1=1 
        """,
        "title": "-------------- 当前订阅用户情况 -----------------",
        "db":1
    },
    {
        "sql": f"""
           select t.cumulate_num '设备数' from analysis_subscribe_number t where t.ref_date = '{yesterdayYmd}' and t.service_key ='zjkj' and t.execute_flag = 0
        """,
        "title": "1.早间服务订阅设备量(昨日)",
        "db":2
    },
    {
        "sql": f"""
       select count(1) '次数', count(DISTINCT(t.active_id)) '设备数' from log_common_service_{yesterdayYYMMdd} t where t.`key` = 'createTaskResult'  and JSON_EXTRACT(t.msg,'$.result') ='1' 
        """,
        "title": "2.1创建定时任务成功量(昨日)",
        "db":2
    },
    {
        "sql": f"""
       select JSON_EXTRACT(t.msg,'$.reason')  '原因',count(1) '次数'  from log_common_service_{yesterdayYYMMdd} t where t.`key` = 'createTaskResult'  and JSON_EXTRACT(t.msg,'$.result') = '0'  group by JSON_EXTRACT(t.msg,'$.reason')
        """,
        "title": "2.2创建定时任务失败量(昨日) ",
        "db":2
    },
    {
        "sql": f"""
       select tt.原因, count(1) '次数' from (
select JSON_EXTRACT(t.msg,'$.reason')  '原因',active_id,count(1) '次数'  from log_common_service_{yesterdayYYMMdd} t where t.`key` = 'createTaskResult'  and JSON_EXTRACT(t.msg,'$.result') = '0'
 group by JSON_EXTRACT(t.msg,'$.reason'),t.active_id) tt group by tt.原因
        """,
        "title": "2.2创建定时任务失败量(昨日)(激活id去重)",
        "db":2
    },
    {
        "sql": f"""
        select '暂无计算'  from dual
        """,
        "title": "3.AI关机的设备量",
        "db":2
    },
    {
        "sql": f"""
        select count(DISTINCT(t.active_id)) '设备数' from log_common_service_{todayYYMMdd} t where t.`key` = 'cachePreStart' 
        """,
        "title": "4.缓存任务准备启动",
        "db":2
    },
    {
        "sql": f"""
        select count(1) '次数'  , count(DISTINCT(t.active_id)) '设备量' from log_common_service_{todayYYMMdd} t where t.`key` = 'cacheResult'
         and JSON_EXTRACT(t.msg,'$.result') =1
        """,
        "title": "5.1缓存任务执行成功量",
        "db":2
    },
    {
        "sql": f"""
      select * from (
           select '总量' as 'reason', count(1) '次数' from log_common_service_{todayYYMMdd} t where t.`key` = 'cacheResult' 
             and JSON_EXTRACT(t.msg,'$.result') =0
      union all      
        select JSON_EXTRACT(t.msg,'$.reason')  'reason',count(1) '次数'  from log_common_service_{todayYYMMdd} t where t.`key` = 'cacheResult' 
    and JSON_EXTRACT(t.msg,'$.result') = 0  AND length(JSON_EXTRACT(t.msg,'$.reason'))<50   group by JSON_EXTRACT(t.msg,'$.reason') ) ttt order by ttt.次数 desc limit 10
        """,
        "title": "5.2缓存任务执行失败量",
        "db":2
    },
    {
        "sql": f"""
       select * from (
      select '总量' as 'reason',   count(DISTINCT(t.active_id)) '次数' from log_common_service_{todayYYMMdd} t where t.`key` = 'cacheResult' 
             and JSON_EXTRACT(t.msg,'$.result') =0
        union all 
		select tt.reason, count(1) '次数' from (
        select JSON_EXTRACT(t.msg,'$.reason')  'reason',count(1) '次数',t.active_id  from log_common_service_{todayYYMMdd} t where t.`key` = 'cacheResult' 
		and JSON_EXTRACT(t.msg,'$.result') = 0  AND length(JSON_EXTRACT(t.msg,'$.reason'))<50  group by JSON_EXTRACT(t.msg,'$.reason'),t.active_id) tt group by tt.reason) ttt order by ttt.次数 desc limit 10
        """,
        "title": "5.2缓存任务执行失败量(激活id去重)",
        "db":2
    },
    {
        "sql": f"""
        select count(1) '次数', count(DISTINCT(t.active_id)) '设备数' from log_common_service_{todayYYMMdd} t where t.`key` = 'tipTaskPreStart'
        """,
        "title": "6.早间播报准备启动",
        "db":2
    },
    {
        "sql": f"""
    select count(1) '次数',count(DISTINCT(t.active_id)) '设备数' from log_common_service_{todayYYMMdd} t where t.`key` = 'morningCallStart' 
    """,
        "title": "6.1早间播报成功启动设备量",
        "db": 2
    },
    {
        "sql": f"""
        select * from (
select '总量' as '原因',count(1) '次数' from log_common_service_{todayYYMMdd} t where t.`key` = 'tipTaskStart' and
JSON_EXTRACT(t.msg,'$.result') = 0
union all 
select JSON_EXTRACT(t.msg,'$.reason')  '原因',count(1) '次数'  from log_common_service_{todayYYMMdd} t where t.`key` = 'tipTaskStart' 
 and JSON_EXTRACT(t.msg,'$.result') =0 group by JSON_EXTRACT(t.msg,'$.reason'))ttt order by ttt.次数 desc;
        """,
        "title": "6.2早间播报失败数",
        "db":2
    },
    {
        "sql": f"""
    select * from (
select '总量' as '原因',count(DISTINCT(t.active_id)) '次数' from log_common_service_{todayYYMMdd} t where t.`key` = 'tipTaskStart' and
JSON_EXTRACT(t.msg,'$.result') = 0
union all     
select tt.原因,count(1) '次数' from (
select JSON_EXTRACT(t.msg,'$.reason')  '原因',count(1) '次数',t.active_id  from log_common_service_{todayYYMMdd} t where t.`key` = 'tipTaskStart' and
  JSON_EXTRACT(t.msg,'$.result') =0 group by JSON_EXTRACT(t.msg,'$.reason') ,t.active_id) tt group by tt.原因)ttt order by ttt.次数 desc
        """,
        "title": "6.2早间播报失败数(激活id去重)",
        "db":2
    },
    {
        "sql": f"""
            select count(1) '次数', count(DISTINCT(t.active_id)) '设备数' from log_common_service_{todayYYMMdd} t where t.`key` = 'sceneStartUp' 
            and JSON_EXTRACT(t.msg,'$.scene') ='MUSIC'
        """,
        "title": "7.音乐场景启动次数",
        "db":2
    },
    {
        "sql": f"""
            select count(1) '次数',  count(DISTINCT(t.active_id)) '设备数' from log_common_service_{todayYYMMdd} t where t.`key` = 'sceneFinish' 
            and JSON_EXTRACT(t.msg,'$.scene') ='MUSIC' and JSON_EXTRACT(t.msg,'$.result') = 1
        """,
        "title": "8.1音乐播放正常结束数",
        "db":2
    },
    {
        "sql": f"""
            select count(1) '次数',  count(DISTINCT(t.active_id)) '设备数' from log_common_service_{todayYYMMdd} t where t.`key` = 'sceneFinish' 
            and JSON_EXTRACT(t.msg,'$.scene') ='MUSIC' and JSON_EXTRACT(t.msg,'$.result') = 0
        """,
        "title": "8.2音乐播放异常结束数",
        "db":2
    },
    {
        "sql": f"""
        select count(1) '次数', count(DISTINCT(t.active_id)) '设备数' from log_common_service_{todayYYMMdd} t where t.`key` = 'sceneStartUp' and JSON_EXTRACT(t.msg,'$.scene') ='WEATHER'
    """,
        "title": "9.天气场景启动次数",
        "db": 2
    },
    {
        "sql": f"""
        select count(1) '次数',  count(DISTINCT(t.active_id)) '设备数' from log_common_service_{todayYYMMdd} t where t.`key` = 'sceneFinish' 
        and JSON_EXTRACT(t.msg,'$.scene') ='WEATHER' and JSON_EXTRACT(t.msg,'$.result') = 1
    """,
        "title": "10.1天气播放正常结束数",
        "db": 2
    },
    {
        "sql": f"""
        select count(1) '次数',  count(DISTINCT(t.active_id)) '设备数' from log_common_service_{todayYYMMdd} t where t.`key` = 'sceneFinish' 
        and JSON_EXTRACT(t.msg,'$.scene') ='WEATHER' and JSON_EXTRACT(t.msg,'$.result') = 0
    """,
        "title": "10.2天气播放异常结束数",
        "db": 2
    },
    {
        "sql": f"""
        select count(1) '次数',  count(DISTINCT(t.active_id)) '设备数' from log_common_service_{todayYYMMdd} t where t.`key` = 'standbyTipShow' 
        """,
        "title": "11.进入倒计时关机弹窗",
        "db":2
    },
    {
        "sql": f"""
            select (case when tt.type = "1" then '到点自动退出' when tt.type = "2" then '用户退出'  else '其他' end ) as '结果' ,num '次数' from (
select JSON_EXTRACT(t.msg,'$.reason')  type ,count(1) num  from log_common_service_{todayYYMMdd} t where t.`key` = 'standbyTipFin' 
 group by JSON_EXTRACT(t.msg,'$.reason') order by count(1) desc ) tt
        """,
        "title": "12.倒计时弹窗退出",
        "db":2
    },
    {
        "sql": f"""
        select count(1) '次数', count(DISTINCT(t.active_id)) '设备数' from log_common_service_{todayYYMMdd} t where t.`key` = 'morningCallFinish' 
        """,
        "title": "13.早间提醒执行结果",
        "db":2
    }
)


def getMysqlDataConnection():
    pool = PooledDB(pymysql, 2, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407, cursorclass=pymysql.cursors.DictCursor)  # 1为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

def getMysqlConfConnection():
    pool = PooledDB(pymysql, 2, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_conf', port=3307, cursorclass=pymysql.cursors.DictCursor)  # 1为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

from datetime import datetime

now = datetime.now()
curTime = now.strftime("%Y-%m-%d %H:%M:%S")

url = "https://open.feishu.cn/open-apis/bot/v2/hook/4b102c84-338a-4bef-87f9-441ecd3f737b"

msgs = []
for item in execRules:
    if item["db"] == 1:
        conn, cur = getMysqlConfConnection()
    else:
        conn, cur = getMysqlDataConnection()
    df = pd.read_sql(item["sql"], con=conn)
    title = item["title"]
    msg = tabulate(df, headers='keys', tablefmt='pretty')
    str = """ 
{title}
{msg}
    """.format(
        msg=msg,
        title=title
    )
    msgs.append(str)

msg_content = "{env}\n{curTime}\n{msg}".format(env=env, curTime=yesterdayYmd, msg="".join(msgs))

print(msg_content)

payload = {
    "msg_type": "text",
    "content": {
        "text": msg_content
    }
}

headers = {
    'Content-Type': 'application/json'
}

data = json.dumps(payload)

# 按照utf-8编码成字节码
data = data.encode("utf-8")

#response = requests.post(url, data=data, headers=headers)
#print(response.text)
